package com.choosefine.it.common.excel;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

/**
 * 简单适配可支持2003/2007版，使用POI，采用泛型跟反射，从数据库中导入、导出Excel
 * 
 * @author liaozhanggen 2017年1月6日
 */
public class ImportExcel<T> {
	
	private Logger logger = LoggerFactory.getLogger(ImportExcel.class);

	private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	
	private Workbook workBook;
	private Class<T> entityClass;
	
	public ImportExcel(String fileName,InputStream is){
		try {
			if (".xlsx".endsWith(fileName)) {	
				workBook = new XSSFWorkbook(is);
			} else if (".xls".endsWith(fileName)) {
				workBook = new HSSFWorkbook(is);
			}else{
				workBook = null;
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public ImportExcel(MultipartFile file){
		try {
			if (file.getOriginalFilename().endsWith(".xlsx")) {
				workBook = new XSSFWorkbook(file.getInputStream());
			} else if (file.getOriginalFilename().endsWith(".xls")) {
				workBook = new HSSFWorkbook(file.getInputStream());
			}else{
				workBook = null;
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
 

	/**
	 * 将 excel 表中的信息导入到数据库
	 * 
	 * @author liaozhanggen 2017年1月6日
	 */
	@SuppressWarnings("deprecation")
	public List<T> getAllByExcel(Class<T> entityClass,int start, int end) {

		this.entityClass = entityClass;
		List<T> list = null;
		try {
			Sheet sheet = workBook.getSheetAt(0);
			int rowNum = sheet.getPhysicalNumberOfRows();
			if (rowNum > 0) {
				list = new ArrayList<>();
				if (end > rowNum) 
					end = rowNum;
				if (start <= 0) 
					return null;
				for (int i = start - 1; i <= end - 1; i++) {
					logger.info("------getAllByExcel----------->" + i);
					Map<Integer, Object> map = new HashMap<>();
					Row row = sheet.getRow(i);
					int cellNum = row.getPhysicalNumberOfCells();
					for (int j = 0; j < cellNum; j++) {
						Cell cell = row.getCell(j);
						
						if (cell != null) {
							switch (cell.getCellType()) {
							case Cell.CELL_TYPE_STRING:
								map.put(j, cell.getRichStringCellValue());
								break;

							case Cell.CELL_TYPE_NUMERIC:
								Object inputValue = null;// 单元格值
								
								if(DateUtil.isCellDateFormatted(cell)){// 判断单元格是否属于日期格式  
									inputValue = cell.getDateCellValue();//java.util.Date类型  
								}else{
									double doubleVal = cell.getNumericCellValue();
									long longVal = Math.round(cell.getNumericCellValue());
									if (Double.parseDouble(longVal+".0")==doubleVal)
										inputValue=longVal;
									else
										inputValue=doubleVal;
								}
								
								map.put(j, inputValue);
								break;
								
							case Cell.CELL_TYPE_BOOLEAN:
								map.put(j, cell.getBooleanCellValue());
								break;

							case Cell.CELL_TYPE_FORMULA:
								map.put(j, cell.getCellFormula());
								break;
							}
						}
					}
					list.add(setObject(map));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	private T setObject(Map<Integer, Object> param) {
		try {
			T tobj = entityClass.newInstance();
			Class cl = tobj.getClass();
			Field[] fields = cl.getDeclaredFields();

			for (int i = 0; i < fields.length; i++) {
				if (param.get(i) != null) {
					String propertyName = fields[i].getName();
					logger.info("--------setObject--------->" + propertyName);
					String methodName = "set" + propertyName.substring(0, 1).toUpperCase() + propertyName.substring(1, propertyName.length());

					Class typeclass = fields[i].getType();
					Method method = cl.getMethod(methodName, new Class[] { typeclass });

					if (typeclass == String.class) {
						method.invoke(tobj, new Object[]{ param.get(i).toString().trim() });

					} else if (typeclass == Integer.class || typeclass == int.class) {
						method.invoke(tobj, new Object[] { Integer.parseInt(param.get(i).toString().trim()) });

					} else if (typeclass == Double.class) {
						method.invoke(tobj, new Object[]{ Double.parseDouble(param.get(i).toString().trim()) });

					} else if (typeclass == Float.class) {
						method.invoke(tobj, new Object[]{ Float.parseFloat(param.get(i).toString().trim()) });

					} else if (typeclass == Date.class) {
						String source = sdf.format(param.get(i));  //2015-02-09  format()才是格式化
						method.invoke(tobj, new Object[]{ sdf.parse(source) });

					}
				}
			}
			return tobj;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}